drop table jms_dm.dm_mng_cn_project_order_taking_detail_dt;
create table jms_dm.dm_mng_cn_project_order_taking_detail_dt
(
    project_name              varchar(100) comment '项目名称',
    code                      varchar(100) comment '客户编码',
    customer_network_code     varchar(100) comment '客户所属网点code',
    customer_agent_code       varchar(100) comment '客户所属代理区code',
    name                      varchar(100) comment '客户名称',
    customer_network_name     varchar(100) comment '客户所属网点name',
    customer_center_code      varchar(100) comment '客户所属中心code',
    customer_center_name      varchar(100) comment '客户所属中心name',
    customer_agent_name       varchar(100) comment '客户所属代理区name',
    order_id                  varchar(100) comment '订单编号',
    waybill_no                varchar(100) comment '运单号',
    pick_time                 varchar(100) comment '取件时间(揽收时间)',
    input_time                varchar(100) comment '订单录入时间',
    add6h_input_time          varchar(100) comment '录入时间往后+6小时',
    date_time                 date comment '日期(录入时间往后+6小时)',
    add6h_timely_pick_falg    int comment '1代表及时揽收标识',
    real_pick_network_code    varchar(100) comment '实际取件网点code(揽收网点)',
    real_pick_network_name    varchar(100) comment '实际取件网点名称(揽收网点)',
    dispatch_network_code     varchar(100) comment '派件网点code',
    dispatch_network_name     varchar(100) comment '派件网点名称',
    cancel_flag               int comment '订单取消1代表取消,0代表未取消',
    order_status_code         int comment '订单状态code',
    real_pick_provider_desc   varchar(100) comment '(揽收网点)实际取件网点省desc',
    real_pick_city_desc       varchar(100) comment '(揽收网点)实际取件网点市desc',
    dispatch_provider_desc    varchar(100) comment '派件网点省desc',
    dispatch_city_desc        varchar(100) comment '派件网点市desc',
    arbitration_flag          int comment '1代表丢失',
    platform_effect_days      int comment '平台时效天数',
    platform_effect_falg      int comment '1代表有平台时效',
    sign_time                 varchar(100) comment '签收时间',
    sign_network_code         varchar(100) comment '签收网点code',
    sign_network_name         varchar(100) comment '签收网点名称',
    way_input_time            varchar(100) comment '运单录入时间',
    waybill_status_code       varchar(100) comment '运单状态code',
    sign_falg                 int comment '签收标识1正常签收和退件签收 0未签收',
    is_waybill_flag           int comment '1实际揽收指标(产生运单)',
    problem_type_code         varchar(100) comment '破损量的问题件类型编码',
    problem_type_name         varchar(100) comment '破损量的问题件类型名称',
    problem_type_subject_code varchar(100) comment '破损量的问题件二级类型编码',
    problem_type_subject_name varchar(100) comment '破损量的问题件二级类型名称',
    express_flag              int comment '1代表破损标识',
    m6_addeff_pick_time       varchar(100) comment '揽收时间加上时效天数的时间+6天 +6小时',
    m7_addeff_pick_time       varchar(100) comment '揽收时间加上时效天数的时间+7天+6小时',
    pick_date_time            date comment '日期(揽收时间加上时效天数的时间) +6小时',
    agent_code                varchar(100) comment '实际取件网点code(揽收网点)代理区code',
    agent_name                varchar(100) comment '实际取件网点code(揽收网点)代理区name',
    center_code               varchar(100) comment '实际取件网点code(揽收网点)中心code',
    center_name               varchar(100) comment '实际取件网点code(揽收网点)中心name'
) ENGINE = OLAP DUPLICATE KEY(project_name,code,customer_network_code,customer_agent_code)
COMMENT '菜鸟项目报表明细时效'
PARTITION BY RANGE(pick_date_time)
(START ('2022-08-01') END ('2023-03-01') EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(waybill_no) BUCKETS 2
PROPERTIES (
'replication_num' = '3',
'dynamic_partition.enable' = 'true',
'dynamic_partition.time_unit' = 'DAY',
'dynamic_partition.time_zone' = 'Asia/Shanghai',
'dynamic_partition.start' = '-180',
'dynamic_partition.end' = '3',
'dynamic_partition.prefix' = 'p',
'dynamic_partition.buckets' = '2',
'in_memory' = 'false',
'storage_format' = 'V2'
);



